﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Business;
using Model;
using System.Web.Security;
using MB.Util;
using System.Data.OleDb;
using System.Data;
using System.Transactions;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace MvcAppWeb.Controllers
{
    public class LocationController : BaseDataController
    {
        //
        // GET: /Location/
        #region 货位信息
        public ActionResult LocationMain()//主表
        {
            if (Session["UserInfo"] == null) return RedirectToAction("LogOn", "Account");
            else
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                return View(model);
            }
        }

        public JsonResult LocationCodeList(int IsSetAll)
        {
            var retData = new object();
            BLocation nbl = new BLocation();
            retData = nbl.GetLocationCodeList(IsSetAll).ToList();
            return Json(retData);
        }
        public JsonResult GoodsCodeList(int IsSetAll, int IsSetNone = 1, string Beginwith = "")
        {
            var retData = new object();
            BLocation nbl = new BLocation();
            retData = nbl.GetGoodsCodeList(IsSetAll, IsSetNone, Beginwith).ToList();
            return Json(retData);
        }
        public JsonResult WarehouseList(int IsSetAll)
        {
            var retData = new object();
            BLocation nbl = new BLocation();
            retData = nbl.GetWarehouseList(IsSetAll).ToList();
            return Json(retData);
        }

        /// <summary>
        /// 校验货位编码唯一性
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public string checkLocation()
        {
            string sjson = string.Empty;
            string code = Request["txtLocationCode"];

            BLocation nbl = new BLocation();
            Location checklocation = nbl.CheckLocation(code);
            if (checklocation != null)
            {
                sjson = "{success:false,msg:'该货位编码已存在！'}";
            }
            else
            {
                sjson = "{success:true}";
            }
            return sjson;
        }

        public JsonResult Query(string SWAREHOUSEID, string SLOCATIONCODE, string SLOCATIONTYPE, string SArea,
            string SRoadWay, string SSTATUS)
        {
            int page = int.Parse(Request["page"].ToString());
            int rows = int.Parse(Request["rows"].ToString());
            int total = 0;
            int n = 0;
            string where = " where 1=1";
            if (!string.IsNullOrEmpty(SWAREHOUSEID))
            {
                if (int.TryParse(SWAREHOUSEID, out n))
                {
                    if (Convert.ToInt32(SWAREHOUSEID) != 0)
                    {
                        where += " and l.warehouseid='" + SWAREHOUSEID + "'";
                    }
                }
                else
                {
                    where += " and 1=2";
                }
            }
            if (!string.IsNullOrEmpty(SLOCATIONCODE))
            {
                where += " and l.LOCATIONCODE like'%" + SLOCATIONCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SLOCATIONTYPE))
            {
                where += " and l.LOCATIONTYPE like'%" + SLOCATIONTYPE + "%'";
            }
            if (!string.IsNullOrEmpty(SArea))
            {
                where += " and l.Area like'%" + SArea + "%'";
            }
            if (!string.IsNullOrEmpty(SRoadWay))
            {
                where += " and l.RoadWay like'%" + SRoadWay + "%'";
            }
            if (!string.IsNullOrEmpty(SSTATUS))
            {
                where += " and l.STATUS ='" + SSTATUS + "'";
            }
            BLocation nc = new BLocation();
            var result = nc.GetLocationList(page, rows, out total, where);

            Dictionary<string, object> json = new Dictionary<string, object>();
            json.Add("total", total);
            json.Add("rows", result);
            return Json(json, JsonRequestBehavior.AllowGet);
        }

        [HttpPost]
        public string Saves(string LOCATIONID)
        {
            string sjson = string.Empty;
            try
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                string LocationCode = Request["txtLocationCode"];
                string LocationType = Request["LocationType"];
                string GOODSRACK = Request["txtGOODSRACK"];
                string Tier = Request["txtTier"];
                string Columns = Request["txtColumns"];
                string Area = Request["txtArea"];
                string RoadWay = Request["txtRoadWay"];
                string Volume = Request["txtVolume"];
                string Remark = Request["txtRemark"];
                string WarehouseID = Request["WAREHOUSEID"];
                string CellNo = Request["CellNo"];
                Location location = new Location();

                sjson = CheckLocation(LOCATIONID, LocationCode, LocationType, WarehouseID, CellNo, GOODSRACK, Tier, Columns, Area,
                    RoadWay, Volume, Remark, out location);

                if (!string.IsNullOrEmpty(sjson))
                    return sjson;

                BLocation nbl = new BLocation();
                if (string.IsNullOrEmpty(LOCATIONID))
                {
                    nbl.AddLocation(location);
                }
                else
                {
                    location.LOCATIONID = int.Parse(LOCATIONID);
                    nbl.EditLocationByID(location);
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;
        }

        [HttpPost]
        public string Delete(string ids)
        {
            string sjson = string.Empty;
            try
            {
                BLocation nbl = new BLocation();
                foreach (var id in ids.Split(','))
                {
                    nbl.DeleteLocation(int.Parse(id));
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;

        }

        [HttpPost]
        public string ImportLocation(HttpPostedFileBase filebase)
        {
            string sjson = string.Empty;
            HttpPostedFileBase file = Request.Files["files"];
            DataTable table = new DataTable();
            string importStr = string.Empty;
            sjson = ImportFile(file, out table, out importStr);
            if (!string.IsNullOrEmpty(sjson))
                return sjson;

            TransactionOptions transactionOption = new TransactionOptions();

            //设置事务隔离级别
            transactionOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

            // 设置事务超时时间为600秒
            transactionOption.Timeout = new TimeSpan(0, 0, 600);

            //引用事务机制，出错时，事物回滚
            using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.Required, transactionOption))
            {
                try
                {
                    if (!string.IsNullOrEmpty(importStr))//上传的是TXT
                    {
                        List<string> lstStr = importStr.Split(';').ToList();
                        foreach (string item in lstStr)
                        {
                            if (string.IsNullOrEmpty(item))
                                continue;
                            List<string> lstModelStr = item.Split(',').ToList();
                            if (lstModelStr.Count != 11)
                            {
                                sjson = "{success:false,msg:'TXT文件内容格式不正确！'}";
                                return sjson;
                            }
                            string LocationCode = lstModelStr[0].ToString();
                            string LocationType = lstModelStr[1].ToString();
                            string WarehouseID = lstModelStr[2].ToString();
                            string CellNo = lstModelStr[3].ToString();
                            string GOODSRACK = lstModelStr[4].ToString();
                            string Tier = lstModelStr[5].ToString();
                            string Columns = lstModelStr[6].ToString();
                            string Area = lstModelStr[7].ToString();
                            string RoadWay = lstModelStr[8].ToString();
                            string Volume = lstModelStr[9].ToString();
                            string Remark = lstModelStr[10].ToString();
                            Location location = new Location();

                            sjson = CheckLocation(string.Empty, LocationCode, LocationType, WarehouseID, CellNo, GOODSRACK, Tier, Columns, Area,
                                RoadWay, Volume, Remark, out location);

                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;

                            BLocation nbl = new BLocation();
                            nbl.AddLocation(location);
                        }
                    }
                    else
                    {
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            string LocationCode = table.Rows[i][0].ToString();
                            string LocationType = table.Rows[i][1].ToString();
                            string WarehouseID = table.Rows[i][3].ToString();
                            string CellNo = table.Rows[i][4].ToString();
                            string GOODSRACK = table.Rows[i][5].ToString();
                            string Tier = table.Rows[i][6].ToString();
                            string Columns = table.Rows[i][7].ToString();
                            string Area = table.Rows[i][8].ToString();
                            string RoadWay = table.Rows[i][9].ToString();
                            string Volume = table.Rows[i][10].ToString();
                            string Remark = table.Rows[i][16].ToString();
                            Location location = new Location();

                            sjson = CheckLocation(string.Empty, LocationCode, LocationType, WarehouseID, CellNo, GOODSRACK, Tier, Columns, Area,
                                RoadWay, Volume, Remark, out location);

                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;

                            BLocation nbl = new BLocation();
                            nbl.AddLocation(location);
                        }
                    }
                    transaction.Complete();
                }
                catch (Exception ex)
                {
                    sjson = "{success:false,msg:'" + ex.Message + "'}";
                    return sjson;
                }
            }
            sjson = "{success:true}";
            System.Threading.Thread.Sleep(2000);
            return sjson;
        }

        public FileResult ExportExcel(int page, int rows)
        {
            //创建Excel文件的对象
            HSSFWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet("Sheet1");
            #region 获取list数据
            int n = 0;
            int total = 0;
            string SWAREHOUSEID = Request["SWAREHOUSEID"];
            string SLOCATIONCODE = Request["txtSLOCATIONCODE"];
            string SLOCATIONTYPE = Request["SLocationType"];
            string SArea = Request["txtSArea"];
            string SRoadWay = Request["txtSRoadWay"];
            string where = " where 1=1";

            if (!string.IsNullOrEmpty(SWAREHOUSEID))
            {
                if (int.TryParse(SWAREHOUSEID, out n))
                {
                    if (Convert.ToInt32(SWAREHOUSEID) != 0)
                    {
                        where += " and l.warehouseid='" + SWAREHOUSEID + "'";
                    }
                }
                else
                {
                    where += " and 1=2";
                }
            }
            if (!string.IsNullOrEmpty(SLOCATIONCODE))
            {
                where += " and l.LOCATIONCODE like'%" + SLOCATIONCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SLOCATIONTYPE))
            {
                where += " and l.LOCATIONTYPE like'%" + SLOCATIONTYPE + "%'";
            }
            if (!string.IsNullOrEmpty(SArea))
            {
                where += " and l.Area like'%" + SArea + "%'";
            }
            if (!string.IsNullOrEmpty(SRoadWay))
            {
                where += " and l.RoadWay like'%" + SRoadWay + "%'";
            }
            BLocation nc = new BLocation();
            List<Location> list = nc.GetLocationList(page, int.MaxValue, out total, where);
            #endregion
            //给sheet1添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(0);
            List<string> lstTitle = new List<string> { "货位编码", "货位类型", "仓库", "仓库ID", "库位", "货架", "层", "列", "区域", "巷道", 
                "体积", "创建人", "创建时间", "修改人", "修改时间", "状态", "备注" };
            for (int i = 0; i < lstTitle.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(lstTitle[i]);
            }
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(list[i].LOCATIONCODE);
                rowtemp.CreateCell(1).SetCellValue(list[i].LOCATIONTYPE);
                rowtemp.CreateCell(2).SetCellValue(list[i].WAREHOUSENAME);
                rowtemp.CreateCell(3).SetCellValue(list[i].WAREHOUSEID);
                rowtemp.CreateCell(4).SetCellValue(list[i].CELLNO);
                rowtemp.CreateCell(5).SetCellValue(list[i].GOODSRACK);
                rowtemp.CreateCell(6).SetCellValue(list[i].TIER.ToString());
                rowtemp.CreateCell(7).SetCellValue(list[i].COLUMNS.ToString());
                rowtemp.CreateCell(8).SetCellValue(list[i].AREA);
                rowtemp.CreateCell(9).SetCellValue(list[i].ROADWAY);
                rowtemp.CreateCell(10).SetCellValue(list[i].VOLUME.ToString());
                rowtemp.CreateCell(11).SetCellValue(list[i].CREATER);
                rowtemp.CreateCell(12).SetCellValue(list[i].CREATEDATE.ToString());
                rowtemp.CreateCell(13).SetCellValue(list[i].EDITOR);
                rowtemp.CreateCell(14).SetCellValue(list[i].EDITDATE.ToString());
                rowtemp.CreateCell(15).SetCellValue(list[i].STATUS == "0" ? "失效" : "生效");
                rowtemp.CreateCell(16).SetCellValue(list[i].REMARK);
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", NewTimeStamp("Location"));
        }

        private string CheckLocation(string LOCATIONID, string LocationCode, string LocationType, string WarehouseID, string CellNo, string GOODSRACK, string Tier, string Columns,
            string Area, string RoadWay, string Volume, string Remark, out Location location)
        {
            string sjson = string.Empty;
            AppUserList model = (AppUserList)Session["UserInfo"];

            location = new Location();
            if (string.IsNullOrEmpty(LocationCode))
            {
                sjson = "{success:false,msg:'货位编码不能为空:" + LocationCode + "！'}";
                return sjson;
            }
            if (string.IsNullOrEmpty(LocationType))
            {
                sjson = "{success:false,msg:'货位类型不能为空:" + LocationCode + "！'}";
                return sjson;
            }
            if (string.IsNullOrEmpty(CellNo) && LocationType != "逻辑货位")
            {
                sjson = "{success:false,msg:'库位不能为空:" + LocationCode + "！'}";
                return sjson;
            }
            if (string.IsNullOrEmpty(WarehouseID))
            {
                sjson = "{success:false,msg:'仓库不能为空:" + LocationCode + "！'}";
                return sjson;
            }
            location.LOCATIONTYPE = LocationType;
            int TWarehouseID = 0;
            if (int.TryParse(WarehouseID, out TWarehouseID))
                location.WAREHOUSEID = TWarehouseID;
            location.GOODSRACK = GOODSRACK;
            decimal TTier = 0;
            if (decimal.TryParse(Tier, out TTier))
                location.TIER = TTier;
            decimal TColumns = 0;
            if (decimal.TryParse(Columns, out TColumns))
                location.COLUMNS = TColumns;
            location.AREA = Area;
            location.ROADWAY = RoadWay;
            decimal TVolume = 0;
            if (decimal.TryParse(Volume, out TVolume))
                location.VOLUME = TVolume;
            location.REMARK = Remark;
            location.LOCATIONCODE = LocationCode;
            location.CREATER = model.USERNAME;
            location.CREATEDATE = DateTime.Now;
            location.EDITOR = model.USERNAME;
            location.EDITDATE = DateTime.Now;
            location.CELLNO = CellNo;
            BLocation nbl = new BLocation();
            Location checklocation = nbl.CheckLocation(LocationCode, LOCATIONID);

            if (checklocation != null)
            {
                sjson = "{success:false,msg:'该货位编码已存在:" + LocationCode + "！'}";
                return sjson;
            }
            return sjson;
        }
        #endregion

        #region 库位商品关联
        public ActionResult LocationGoods()//库位商品关联
        {
            if (Session["UserInfo"] == null) return RedirectToAction("LogOn", "Account");
            else
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                return View(model);
            }
        }

        public JsonResult QueryLocationGoods(string SLOCATIONCODE, string SGOODSCODE, string INVGOODSCODE, string SGOODSNAME, string INVGOODSNAME)
        {
            int page = int.Parse(Request["page"].ToString());
            int rows = int.Parse(Request["rows"].ToString());
            int total = 0;
            string where = " where 1=1 and lo.STATUS=1 ";
            if (!string.IsNullOrEmpty(SLOCATIONCODE))
            {
                where += " and lo.LOCATIONCODE like'%" + SLOCATIONCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SGOODSCODE))
            {
                where += " and g.GOODSCODE like'%" + SGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSCODE))
            {
                where += " and g2.GOODSCODE like'%" + INVGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SGOODSNAME))
            {
                where += " and g.GOODSNAME like'%" + SGOODSNAME + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSNAME))
            {
                where += " and g2.GOODSNAME like'%" + INVGOODSNAME + "%'";
            }
            BLocation nc = new BLocation();
            var result = nc.GetLocationGoodsList(page, rows, out total, where);

            Dictionary<string, object> json = new Dictionary<string, object>();
            json.Add("total", total);
            json.Add("rows", result);
            return Json(json, JsonRequestBehavior.AllowGet);
        }

        [HttpPost]
        public string checkLocationExist(string code)
        {
            string sjson = string.Empty;

            BLocation nbl = new BLocation();
            Location checklocation = nbl.CheckLocation(code);
            if (checklocation != null)
            {
                sjson = "{success:true}";
            }
            else
            {
                sjson = "{success:false,msg:'该货位编码不存在！'}";
            }
            return sjson;
        }

        [HttpPost]
        public string checkGoodsExist()
        {
            string sjson = string.Empty;
            string code = Request["txtGoodsCode"];

            BLocation nbl = new BLocation();
            Goods check = nbl.CheckGoods(code);
            if (check != null)
            {
                sjson = "{success:true}";
            }
            else
            {
                sjson = "{success:false,msg:'该商品编码不存在！'}";
            }
            return sjson;
        }

        [HttpPost]
        public string SaveLocationGoods(string LOCATIONGOODSID)
        {
            string sjson = string.Empty;
            try
            {
                string LocationID = Request["LocationCode"];
                //string GoodsID = Request["GoodsCode"];
                string GoodsCode = Request["txtGoodsCode"];
                string DirectSellingGoodsCode = Request["txtDirectSellingGoodsCode"];
                string Valid = Request["Valid"];
                //string DirectSellingGoodsID = Request["DirectSellingGoodsCode"];
                string Remark = Request["txtRemark"];
                LocationGoods _LocationGoods = new LocationGoods();
                sjson = CheckLocationGoods(LOCATIONGOODSID, string.Empty, LocationID, GoodsCode, string.Empty,
                    DirectSellingGoodsCode, string.Empty,
                    Remark, out _LocationGoods, Valid);

                if (!string.IsNullOrEmpty(sjson))
                    return sjson;

                BLocation nbl = new BLocation();
                if (string.IsNullOrEmpty(LOCATIONGOODSID))
                {
                    nbl.AddLocationGoods(_LocationGoods);
                }
                else
                {
                    _LocationGoods.LOCATIONGOODSID = int.Parse(LOCATIONGOODSID);
                    nbl.EditLocationGoodsByID(_LocationGoods);
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;
        }

        [HttpPost]
        public string DeleteLocationGoods(string ids)
        {
            string sjson = string.Empty;
            try
            {
                BLocation nbl = new BLocation();
                foreach (var id in ids.Split(','))
                {
                    nbl.DeleteLocationGoods(int.Parse(id));
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;

        }

        [HttpPost]
        public string ImportLocationGoods(HttpPostedFileBase filebase)
        {
            string sjson = string.Empty;
            HttpPostedFileBase file = Request.Files["files"];
            DataTable table = new DataTable();
            string importStr = string.Empty;
            sjson = ImportFile(file, out table, out importStr);
            if (!string.IsNullOrEmpty(sjson))
                return sjson;

            TransactionOptions transactionOption = new TransactionOptions();

            //设置事务隔离级别
            transactionOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

            // 设置事务超时时间为600秒
            transactionOption.Timeout = new TimeSpan(0, 0, 600);

            //引用事务机制，出错时，事物回滚
            using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.Required, transactionOption))
            {
                try
                {
                    if (!string.IsNullOrEmpty(importStr))//上传的是TXT
                    {
                        List<string> lstStr = importStr.Split(';').ToList();
                        foreach (string item in lstStr)
                        {
                            if (string.IsNullOrEmpty(item))
                                continue;
                            List<string> lstModelStr = item.Split(',').ToList();
                            if (lstModelStr.Count != 3)
                            {
                                sjson = "{success:false,msg:'TXT文件内容格式不正确！'}";
                                return sjson;
                            }
                            string LocationCode = lstModelStr[0].ToString();
                            string GoodsCode = lstModelStr[1].ToString();
                            string ProcessGoodsCode = lstModelStr[2].ToString();
                            string DirectSellingGoodsCode = lstModelStr[3].ToString();
                            string WOCGoodsCode = lstModelStr[4].ToString();
                            string OtherGoodsCode = lstModelStr[5].ToString();
                            string Remark = lstModelStr[6].ToString();
                            LocationGoods _LocationGoods = new LocationGoods();
                            sjson = CheckLocationGoods(string.Empty, LocationCode, string.Empty, GoodsCode, string.Empty,
                                DirectSellingGoodsCode, string.Empty, Remark, out _LocationGoods);
                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;
                            BLocation nbl = new BLocation();
                            nbl.AddLocationGoods(_LocationGoods);
                        }
                    }
                    else
                    {
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            string LocationCode = table.Rows[i][0].ToString();
                            string GoodsCode = table.Rows[i][1].ToString();
                            string DirectSellingGoodsCode = table.Rows[i][5].ToString();
                            string Remark = table.Rows[i][7].ToString();
                            LocationGoods _LocationGoods = new LocationGoods();

                            sjson = CheckLocationGoods(string.Empty, LocationCode, string.Empty, GoodsCode, string.Empty,
                                DirectSellingGoodsCode, string.Empty, Remark, out _LocationGoods);

                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;
                            BLocation nbl = new BLocation();
                            nbl.AddLocationGoods(_LocationGoods);
                        }
                    }
                    transaction.Complete();
                }
                catch (Exception ex)
                {
                    sjson = "{success:false,msg:'" + ex.Message + "'}";
                    return sjson;
                }
            }
            sjson = "{success:true}";
            System.Threading.Thread.Sleep(2000);
            return sjson;
        }

        [HttpPost]
        public FileResult ExportExcelLocationGoods(int page, int rows)//导出execl
        {
            //创建Excel文件的对象
            HSSFWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet("Sheet1");
            #region 获取list数据
            int total = 0;
            string SLOCATIONCODE = Request["txtSLOCATIONCODE"];
            string SGOODSCODE = Request["txtSGOODSCODE"];
            string INVGOODSCODE = Request["txtINVGOODSCODE"];
            string SGOODSNAME = Request["txtSGOODSNAME"];
            string INVGOODSNAME = Request["txtINVGOODSNAME"];
            string where = " where 1=1";
            if (!string.IsNullOrEmpty(SLOCATIONCODE))
            {
                where += " and lo.LOCATIONCODE like'%" + SLOCATIONCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SGOODSCODE))
            {
                where += " and g.GOODSCODE like'%" + SGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSCODE))
            {
                where += " and g2.GOODSCODE like'%" + INVGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SGOODSNAME))
            {
                where += " and g.GOODSNAME like'%" + SGOODSNAME + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSNAME))
            {
                where += " and g2.GOODSNAME like'%" + INVGOODSNAME + "%'";
            }
            BLocation nc = new BLocation();
            List<LocationGoods> list = nc.GetLocationGoodsList(page, int.MaxValue, out total, where);
            #endregion
            //给sheet1添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(0);
            List<string> lstTitle = new List<string> { "货位编码", "上架商品编码", "上架商品名称", "仓库", "库位",
                "下架商品编码", "下架商品名称", "备注" };
            for (int i = 0; i < lstTitle.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(lstTitle[i]);
            }
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(list[i].LOCATIONCODE);
                rowtemp.CreateCell(1).SetCellValue(list[i].GOODSCODE);
                rowtemp.CreateCell(2).SetCellValue(list[i].GOODSNAME);
                rowtemp.CreateCell(3).SetCellValue(list[i].WAREHOUSENAME);
                rowtemp.CreateCell(4).SetCellValue(list[i].CELLNO);
                rowtemp.CreateCell(5).SetCellValue(list[i].DIRECTSELLINGGOODSCODE);
                rowtemp.CreateCell(6).SetCellValue(list[i].DIRECTSELLINGGOODSNAME);
                rowtemp.CreateCell(7).SetCellValue(list[i].REMARK);
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", NewTimeStamp("LocationGoods"));
        }

        private string CheckLocationGoods(string LOCATIONGOODSID, string LocationCode, string LocationID, string GoodsCode, string GoodsID,
             string DirectSellingGoodsCode, string DirectSellingGoodsID,
            string Remark, out LocationGoods locationgoods, string Valid = "0")
        {
            string sjson = string.Empty;
            AppUserList model = (AppUserList)Session["UserInfo"];
            locationgoods = new LocationGoods();
            locationgoods.REMARK = Remark;
            locationgoods.CREATER = model.USERNAME;
            locationgoods.CREATEDATE = DateTime.Now;
            locationgoods.EDITOR = model.USERNAME;
            locationgoods.EDITDATE = DateTime.Now;
            int valid = 0;
            int.TryParse(Valid, out valid);
            locationgoods.VALID = valid;
            BLocation nbl = new BLocation();
            if (!string.IsNullOrEmpty(LocationCode))
            {
                Location checklocation = nbl.CheckLocation(LocationCode);
                if (checklocation != null)
                {
                    locationgoods.LOCATIONID = checklocation.LOCATIONID;
                }
                else
                {
                    sjson = "{success:false,msg:'货位编码不存在:" + LocationCode + "！'}";
                    return sjson;
                }
            }
            if (!string.IsNullOrEmpty(GoodsCode))
            {
                Goods checkgoods = nbl.CheckGoods(GoodsCode);
                if (checkgoods != null)
                {
                    locationgoods.GOODSID = checkgoods.GOODSID;
                    GoodsID = checkgoods.GOODSID.ToString();
                }
                else
                {
                    sjson = "{success:false,msg:'商品编码不存在:" + GoodsCode + "！'}";
                    return sjson;
                }
            }
            if (!string.IsNullOrEmpty(DirectSellingGoodsCode) && DirectSellingGoodsCode != "0")
            {
                Goods checkgoods = nbl.CheckGoods(DirectSellingGoodsCode);
                if (checkgoods != null)
                {
                    locationgoods.DIRECTSELLINGGOODSID = checkgoods.GOODSID;
                }
                else
                {
                    sjson = "{success:false,msg:'上架商品编码不存在:" + DirectSellingGoodsCode + "！'}";
                    return sjson;
                }
            }
            if (!string.IsNullOrEmpty(LocationID))
                locationgoods.LOCATIONID = int.Parse(LocationID);
            if (!string.IsNullOrEmpty(GoodsID))
                locationgoods.GOODSID = int.Parse(GoodsID);
            if (!string.IsNullOrEmpty(DirectSellingGoodsID))
                locationgoods.DIRECTSELLINGGOODSID = int.Parse(DirectSellingGoodsID);
            List<LocationGoods> _LocationGoods = nbl.CheckLocationGoodsPrimary(locationgoods.LOCATIONID,locationgoods.GOODSID, LOCATIONGOODSID);
            if (_LocationGoods != null && _LocationGoods.Count > 0)
            {
                if (_LocationGoods.Where(_ => _.WAREHOUSECODE == "06").ToList().Count > 0)//货位仓库为分拣仓时，只能对应一个商品
                    sjson = "{success:false,msg:'分拣仓只能关联一个商品:！'}";
                if (_LocationGoods.Where(_ => _.GOODSID == int.Parse(GoodsID)).ToList().Count > 0)//其他仓库根据商品和货位判断唯一性
                    sjson = "{success:false,msg:'货位和商品关联已存在:" + GoodsCode + "！'}";
                return sjson;
            }
            return sjson;
        }
        #endregion
    }
}
